﻿using System;
using System.Data;
using System.Data.DBLib;
using System.Web;
using SysComm;

namespace SysOperationLayer.DBHandle
{
    public class ProductDB : DBBase
    {
        public DataTable getProductList(string modelNo, string revision, string modelName, string specification, string createDateBegin, string createDateEnd, int isDelete)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select * from Product where 1=1";

                if (modelNo != "" && modelNo != null)
                {
                    strSql += " and Model like '%" + modelNo + "%'";
                }
                if (revision != "" && revision != null)
                {
                    strSql += " and Revision like '%" + revision + "%'";
                }
                if (modelName != "" && modelName != null)
                {
                    strSql += " and ModelName like '%" + modelName + "%'";
                }
                if (specification != "" && specification != null)
                {
                    strSql += " and Specification like '%" + specification + "%'";
                }
                if (createDateBegin != "" && createDateEnd != "")
                {
                    strSql += " and CreateDate between '" + createDateBegin + "' and '" + createDateEnd + "'"; ;
                }

                strSql += " and IsDelete=" + isDelete;
                return m_objDB.getDataTable(strSql, "Product");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }


        public DataTable getProductById(string modelNo)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select * from Product where Model='" + modelNo + "'";
                return m_objDB.getDataTable(strSql, "Product");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }


        public DataTable getProjectListByPjCode(string code)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select * from Project where 1=1 ";
                if (code != "" && code != null)
                {
                    strSql += " and ProjectCode like '%" + code + "%'";
                }
                return m_objDB.getDataTable(strSql, "Project");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public int insertProduct(string model, 
                                    string modelName, 
                                    string revision, 
                                    string specification,
                                    string remark1,
                                    string remark2,
                                    string remark3,
                                    string remark4,
                                    string remark5,
                                    string datefld1,
                                    string datefld2,
                                    string datefld3,
                                    string datefld4,
                                    string datefld5,
                                    string decifld1,
                                    string decifld2)        
        {
            int ret = 0;
            string strSql = "";
            string strUserId;
            string strUserIp;
            double dec1 = 0;
            double dec2 = 0;

            if (decifld1 != "")
            {
                dec1 = double.Parse(decifld1);
            }
            if (decifld2 != "")
            {
                dec2 = double.Parse(decifld2);
            }

            strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
            strUserIp = HttpContext.Current.Request.UserHostAddress;

            if (ConnectDB())
            {
                strSql = "insert into Product(Model, ModelName, Revision, Specification,";
                strSql += " Remark1, Remark2, Remark3, Remark4, Remark5,";
                strSql += " DateFld1, DateFld2, DateFld3, DateFld4, DateFld5,";
                strSql += " DeciFld1, DeciFld2,";
                strSql += " CreateIp, CreateBy, CreateDate, UpdateIp, UpdateBy, UpdateDate)";
                strSql += " values('" + model + "','" + modelName + "','" + revision + "','" + specification + "','";
                strSql += remark1 + "','" + remark2 + "','" + remark3 + "','" + remark4 + "','" + remark4 + "','";
                strSql += datefld1 + "','" + datefld2 + "','" + datefld3 + "','" + datefld4 + "','" + datefld5 + "',";
                strSql += dec1 + "," + dec2 + ",'";
                strSql += strUserIp + "','" + strUserId + "',getDate(),'" + strUserIp + "','" + strUserId + "',getDate())";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
                ret = -1;
            }
            return ret;
        }


        public int trashProduct(string model)
        {
            int ret = 0;
            if (ConnectDB())
            {
                string strSQL;
                strSQL = "delete from Product where Model='" + model + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSQL, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            return ret;
        }


        public int deleteProduct(string model)
        {
            int ret = 0;
            string strUserId;
            string strUserIp;
            strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
            strUserIp = HttpContext.Current.Request.UserHostAddress;

            if (ConnectDB())
            {
                string strSql;
                strSql = "update Product set ";
                strSql += " isDelete=1";
                strSql += ", DeleteBy='" + strUserId + "'";
                strSql += ", DeleteIP='" + strUserIp + "'";
                strSql += ", DeleteDate=getDate()";
                strSql += " where Model='" + model + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            return ret;
        }


        public int restoreProduct(string model)
        {
            int ret = 0;
            if (ConnectDB())
            {
                string strSQL;
                strSQL = "update Product set isDelete=0 where Model='" + model + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSQL, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            return ret;
        }


        public int updateProduct(string model,
                                    string modelName,
                                    string revision,
                                    string specification,
                                    string remark1,
                                    string remark2,
                                    string remark3,
                                    string remark4,
                                    string remark5,
                                    string datefld1,
                                    string datefld2,
                                    string datefld3,
                                    string datefld4,
                                    string datefld5,
                                    string decifld1,
                                    string decifld2)
        {
            int ret = 0;
            string strSql = "";
            string strUserId;
            string strUserIp;
            double dec1 = 0;
            double dec2 = 0;

            if (decifld1 != "")
            {
                dec1 = double.Parse(decifld1);
            }
            if (decifld2 != "")
            {
                dec2 = double.Parse(decifld2);
            }

            strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
            strUserIp = HttpContext.Current.Request.UserHostAddress;

            if (model == "" || model == null)
            {
                return -1;
            }
            /*
             select Model, ModelName, Revision, Specification, CreateDate,
            Remark1, Remark2, Remark3, Remark4, Remark5, 
            DateFld1, DateFld2, DateFld3, DateFld4, DateFld5,
            DeciFld1, DeciFld2 from product
             */

            if (ConnectDB())
            {
                strSql = "update Product set ";
                strSql += " ModelName='" + modelName + "'";
                strSql += ", Revision='" + revision + "'";
                strSql += ", Specification='" + specification + "'";
                strSql += ", Remark1='" + remark1 + "'";
                strSql += ", Remark2='" + remark2 + "'";
                strSql += ", Remark3='" + remark3 + "'";
                strSql += ", Remark4='" + remark4 + "'";
                strSql += ", Remark5='" + remark5 + "'";
                strSql += ", DateFld1='" + datefld1 + "'";
                strSql += ", DateFld2='" + datefld2 + "'";
                strSql += ", DateFld3='" + datefld3 + "'";
                strSql += ", DateFld4='" + datefld4 + "'";
                strSql += ", DateFld5='" + datefld5 + "'";
                strSql += ", DeciFld1=" + dec1;
                strSql += ", DeciFld2=" + dec2;
                strSql += ", UpdateBy='" + strUserId + "'";
                strSql += ", UpdateIp='" + strUserIp + "'";
                strSql += ", UpdateDate=getDate()";
                strSql += " where Model='" + model + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
                ret = -1;
            }
            return ret;
        }


        public DataTable getProductProjectList(string modelNo)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select pp.Model, pd.ModelName, pj.ProjectCode, pj.Country, pj.Title, pj.Status";
                strSql += " from productproject pp, project pj, product pd";
                strSql += " where pp.Model=pd.Model and pp.ProjectCode=pj.ProjectCode";
                strSql += " and pp.Model='" + modelNo + "'";
                return m_objDB.getDataTable(strSql, "ProductProject");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }


        public int deleteProductProject(string model, string projectCode)
        {
            int ret = 0;
            if (ConnectDB())
            {
                string strSQL;
                strSQL = "delete from ProductProject where Model='" + model + "' and ProjectCode='" + projectCode + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSQL, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            return ret;
        }


        public int insertProjectProject(string model, string projectCode)
        {
            int ret = 0;
            string strSql = "";
            string strUserId;
            string strUserIp;
            string strOwnerGroupId;


            strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
            strUserIp = HttpContext.Current.Request.UserHostAddress;
            strOwnerGroupId = HttpContext.Current.Session[SysUtil.m_str_session_usergroupid].ToString();

            if (ConnectDB())
            {
                /*
select Model, ProjectCode, CreateBy, CreateDate, UpdateBy, UpdateDate, OwnerGroupID
from productproject
                 */
                strSql = "insert into ProductProject";
                strSql += " values('" + model + "','" + projectCode + "','";
                strSql += strUserId + "',getDate(),'" + strUserId + "',getDate(),'" + strOwnerGroupId + "')";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);

                ret = objError.RetCode;
                m_strMessage = objError.Message;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
                ret = -1;
            }
            return ret;
        }
    }
}
